ALTER PROCEDURE SP_CREATE_UPDATE_DIEBANK_TRANSIT_LOT 
@LOT_ID				INT,
@TOTAL_SHIP_QTY		INT,
@NEW_LOT_NUMBER     VARCHAR(50),
@NEXT_LOCATION      VARCHAR(50),
@NEXT_PROCESS       VARCHAR(50),
@USER_NAME			VARCHAR(50),
@VENDOR_NAME        VARCHAR(50),
@ASSY_RELEASE_FLAG  INT
AS 
BEGIN
	
	DECLARE @SHIPPEDLOTID   INT
	DECLARE @DATE_OUT	DATETIME
	DECLARE @LOCATION_ID INT
	DECLARE @NEW_LOT_ID INT
	DECLARE @PROCESSSTEP_ID	INT
	DECLARE @FAB_LOTNO	VARCHAR(50)
	DECLARE @QTY_IN INT
	DECLARE @BOM_QTY_IN INT
	DECLARE @BOM_LOT_ID INT
	DECLARE @BOM_LOT_QTY INT

	DECLARE @INVENTORY_TYPE VARCHAR(50)
	DECLARE @NEW_PROCESSSTEP_ID INT
	DECLARE @NEWLOCATION_ID INT
	DECLARE @NEW_PROCESS_LOT_ID INT
	DECLARE @ASSEMBLY_RELEASE_ID INT

	declare @shippedlotCount int

	SET @DATE_OUT = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)

	SELECT TOP 1 @ASSEMBLY_RELEASE_ID = ART.ASSEMBLY_RELEASE_ID FROM ASSM_RELEASE_TABLE ART, ASSM_RELEASE_Lot_TABLE ARLT WHERE ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID 
										AND LOT_ID = @LOT_ID AND SHIPPED_LOT_NUMBER = @NEW_LOT_NUMBER AND STATUS <> 'CANCELLED'
 
	IF @NEXT_PROCESS = 'ASSEMBLY' AND @ASSY_RELEASE_FLAG > 0
	BEGIN
			SELECT @FAB_LOTNO = FAB_LOT_NUMBER, @LOCATION_ID= LOCATION_ID, @PROCESSSTEP_ID = PROCESSSTEP_ID FROM LOT_TABLE WHERE LOT_ID = @LOT_ID

			SELECT @NEW_LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @NEW_LOT_NUMBER

			IF @NEW_LOT_ID IS NULL
			BEGIN
					insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, qty_in,  Date_In, Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category,  LOT_DROP_OUT, USER_NAME)
						values (@PROCESSSTEP_ID, @LOCATION_ID, @NEW_LOT_NUMBER, @FAB_LOTNO, @NEW_LOT_NUMBER,  @TOTAL_SHIP_QTY,  @DATE_OUT, 'Close', CURRENT_TIMESTAMP, 0, 'PROD',  0 , @USER_NAME )
					select @NEW_LOT_ID = @@IDENTITY
					--select * from Inventory_Lot
				insert into Inventory_Lot( Lot_ID, Timestamp, IL_Date_In, IL_Qty_In ) values (@NEW_LOT_ID, CURRENT_TIMESTAMP, @DATE_OUT, @TOTAL_SHIP_QTY )
			END
			ELSE
			BEGIN
					UPDATE LOT_TABLE SET QTY_IN = @TOTAL_SHIP_QTY , USER_NAME = @USER_NAME WHERE LOT_ID = @NEW_LOT_ID
					UPDATE Inventory_Lot SET IL_Qty_In = @TOTAL_SHIP_QTY WHERE LOT_ID = @NEW_LOT_ID
			END
			
			select @shippedlotCount = count(ShippedLot_ID) from Shipped_Lot where Lot_ID = @NEW_LOT_ID and Status = 'Close' and Ship_Status > 0 and Ship_Date = @DATE_OUT and Ship_Qty = @TOTAL_SHIP_QTY
		
			IF( @shippedlotCount = 0 )
			BEGIN
				select @SHIPPEDLOTID = ShippedLot_ID from Shipped_Lot where Lot_ID = @NEW_LOT_ID and status = 'TRANSIT' and Ship_Status = 0 and Ship_Date = @DATE_OUT and Ship_Qty = @TOTAL_SHIP_QTY
				--if @shippedLotID is null 
	 			IF ( @@rowcount = 0 )
	 			BEGIN	
					INSERT INTO SHIPPED_LOT( LOT_ID, SHIP_DATE, STATUS, SHIP_QTY, TIMESTAMP, SHIP_STATUS, SL_NEXTLOCATION , SL_NEXT_PROCESS ) VALUES 
								( @NEW_LOT_ID, @DATE_OUT, 'TRANSIT', @TOTAL_SHIP_QTY, CURRENT_TIMESTAMP, 0, @NEXT_LOCATION , @NEXT_PROCESS )
			
					SET @SHIPPEDLOTID = ( SELECT @@IDENTITY )
				END

				UPDATE LOT_TABLE SET QTY_IN = QTY_IN - @TOTAL_SHIP_QTY, USER_NAME = @USER_NAME WHERE LOT_ID = @LOT_ID

				UPDATE ASSM_RELEASE_Lot_TABLE SET LOT_TRANSFERRED = 0 WHERE LOT_ID = @LOT_ID AND SHIPPED_LOT_NUMBER = @NEW_LOT_NUMBER AND ASSEMBLY_RELEASE_ID = @ASSEMBLY_RELEASE_ID
				--TO Decrease the Qty of Bom Lots Used for assembly release In LOt tbale
				DECLARE @REMAIN_SHIp_QTY INT
				SET @REMAIN_SHIp_QTY = @TOTAL_SHIP_QTY

				DECLARE @BOMLOT_CURSOR CURSOR
				SET @BOMLOT_CURSOR = CURSOR FOR SELECT LOT_ID , SUM(Qty_Pulled) FROM BOM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSEMBLY_RELEASE_ID GROUP BY LOT_ID
				OPEN @BOMLOT_CURSOR
				FETCH NEXT FROM @BOMLOT_CURSOR INTO @BOM_LOT_ID, @BOM_LOT_QTY
				WHILE @@FETCH_STATUS = 0
				BEGIN
					IF @BOM_LOT_QTY <= @REMAIN_SHIp_QTY
					BEGIN
						SET @REMAIN_SHIp_QTY = @REMAIN_SHIp_QTY-@BOM_LOT_QTY
						UPDATE LOT_TABLE SET QTY_IN = QTY_IN - @BOM_LOT_QTY, USER_NAME = @USER_NAME WHERE LOT_ID = @BOM_LOT_ID
					END
					ELSE
					BEGIN
						UPDATE LOT_TABLE SET QTY_IN = QTY_IN - @REMAIN_SHIp_QTY, USER_NAME = @USER_NAME WHERE LOT_ID = @BOM_LOT_ID
						SET @REMAIN_SHIp_QTY = 0
					END
					--UPDATE LOT_TABLE SET QTY_IN = QTY_IN - @BOM_LOT_QTY, USER_NAME = @USER_NAME WHERE LOT_ID = @BOM_LOT_ID
					SELECT @BOM_QTY_IN = ISNULL(QTY_IN, 0) FROM LOT_TABLE WHERE LOT_ID = @BOM_LOT_ID

					IF @BOM_QTY_IN = 0
					BEGIN
						UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Close' WHERE LOT_ID = @BOM_LOT_ID
					END
					
					IF @REMAIN_SHIp_QTY = 0
					BEGIN
						BREAK						
					END

					SET @BOM_QTY_IN  = NULL
					SET @BOM_LOT_QTY = NULL
					SET @BOM_LOT_ID  = NULL
					FETCH NEXT FROM @BOMLOT_CURSOR INTO @BOM_LOT_ID, @BOM_LOT_QTY
					
				END
				CLOSE @BOMLOT_CURSOR
				DEALLOCATE @BOMLOT_CURSOR
				--END OF BOM LOT QTy UPDATE

				SELECT @QTY_IN = ISNULL(QTY_IN, 0) FROM LOT_TABLE WHERE LOT_ID = @LOT_ID

				IF @QTY_IN = 0
				BEGIN
					UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Close' WHERE LOT_ID = @LOT_ID
				END
			END
	END	
	ELSE
	BEGIN		
			select @shippedlotCount = count(ShippedLot_ID) from Shipped_Lot where Lot_ID = @lot_id and Status = 'Close' and Ship_Status > 0 and Ship_Date = @DATE_OUT 
			IF( @shippedlotCount = 0 )
			BEGIN	
			
				select @SHIPPEDLOTID = ShippedLot_ID from Shipped_Lot where Lot_ID = @lot_id and status = 'TRANSIT' and Ship_Status = 0 and Ship_Date = @DATE_OUT 
				--if @shippedLotID is null 
	 			IF ( @@rowcount = 0 )
	 			BEGIN	
					INSERT INTO SHIPPED_LOT( LOT_ID, SHIP_DATE, STATUS, SHIP_QTY, TIMESTAMP, SHIP_STATUS, SL_NEXTLOCATION , SL_NEXT_PROCESS ) VALUES 
						( @LOT_ID, @DATE_OUT, 'TRANSIT', @TOTAL_SHIP_QTY, CURRENT_TIMESTAMP, 0, @NEXT_LOCATION , @NEXT_PROCESS )
			
					SET @SHIPPEDLOTID = ( SELECT @@IDENTITY )
				END
				
				UPDATE LOT_TABLE SET QTY_IN = QTY_IN - @TOTAL_SHIP_QTY, USER_NAME = @USER_NAME WHERE LOT_ID = @LOT_ID
				
				SELECT @QTY_IN = ISNULL(QTY_IN, 0) FROM LOT_TABLE WHERE LOT_ID = @LOT_ID

				IF @QTY_IN = 0
				BEGIN
					UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Close' WHERE LOT_ID = @LOT_ID
				END
			END
	END
	
	--WHEN USER TRANSFER FROM SAME VANDOR TO SAME VENDOR
	IF @VENDOR_NAME = @NEXT_LOCATION
	BEGIN
			UPDATE SHIPPED_LOT SET STATUS = 'Close', SHIP_STATUS = 1, Close_DATE = CURRENT_TIMESTAMP WHERE SHIPPEDLOT_ID = @SHIPPEDLOTID 
			
			IF @NEXT_PROCESS = 'FT' OR @NEXT_PROCESS = 'FPO' 
			BEGIN
					SET @INVENTORY_TYPE = 'TEST'
			END
			ELSE IF @NEXT_PROCESS = 'Tape & Reel'  
			BEGIN
					SET @INVENTORY_TYPE = 'Class Store'
			END
			ELSE IF @NEXT_PROCESS = 'FG'  
			BEGIN
					SET @INVENTORY_TYPE = 'Finished Goods'
			END
			ELSE
			BEGIN
					SET @INVENTORY_TYPE = @NEXT_PROCESS
			END
			SELECT @FAB_LOTNO = FAB_LOT_NUMBER, @PROCESSSTEP_ID = PROCESSSTEP_ID FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
			--SELECT DISTINCT @NEW_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( @PROCESSSTEP_ID, 1 ) WHERE PS_TYPE = @NEXT_PROCESS
			
			SELECT TOP 1 @NEW_PROCESSSTEP_ID = Processstep_id FROM ASSM_RELEASE_TABLE ART, ASSM_RELEASE_LOT_TABLE ARLT WHERE ART.Assembly_Release_Id = ARLT.Assembly_Release_Id AND STATUS <> 'CANCELLED' AND shipped_Lot_number like @NEW_LOT_NUMBER +'%' 
			IF @NEW_PROCESSSTEP_ID IS NULL
			BEGIN
					SELECT TOP 1 @NEW_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( @PROCESSSTEP_ID, 1 ) WHERE PS_TYPE = @NEXT_PROCESS	
			END

			SELECT @NEWLOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = @INVENTORY_TYPE AND L_VENDOR_NAME = @VENDOR_NAME
	
			SELECT @NEW_PROCESS_LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @NEW_PROCESSSTEP_ID AND LOCATION_ID = @NEWLOCATION_ID AND LOT_NUMBER = @NEW_LOT_NUMBER 
			
			IF @NEW_PROCESS_LOT_ID IS NULL
			BEGIN
					insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, qty_in,  Date_In, Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category,  LOT_DROP_OUT, USER_NAME) 
						values (@NEW_PROCESSSTEP_ID, @NEWLOCATION_ID, @NEW_LOT_NUMBER, @FAB_LOTNO, @NEW_LOT_NUMBER,  @TOTAL_SHIP_QTY,  @DATE_OUT, 'Running', CURRENT_TIMESTAMP, 0, 'PROD',  0 , @USER_NAME )
					select @NEW_PROCESS_LOT_ID = @@IDENTITY

				IF @INVENTORY_TYPE = 'Die Bank' OR @INVENTORY_TYPE = 'Class Store' OR @INVENTORY_TYPE = 'Finished Goods'
				BEGIN
					--select * from Inventory_Lot
					insert into Inventory_Lot( Lot_ID, Timestamp, IL_Date_In, IL_Qty_In ) values (@NEW_PROCESS_LOT_ID, CURRENT_TIMESTAMP, @DATE_OUT, @TOTAL_SHIP_QTY )		
				END
				ELSE IF @INVENTORY_TYPE = 'Assembly' 
				BEGIN
					--select * from Assembly_Lot
					insert into Assembly_Lot( Lot_ID, Timestamp ) values (@NEW_PROCESS_LOT_ID, CURRENT_TIMESTAMP)		
				END
				ELSE IF @INVENTORY_TYPE = 'TEST' 
				BEGIN
					--select * from Final_Test_Lot
					insert into Final_Test_Lot( Lot_ID, Timestamp ) values (@NEW_PROCESS_LOT_ID, CURRENT_TIMESTAMP)		
				END
				
			END
			--WHEN DIE BAN VENDOR AND ASSY RELEASE VENDOR ARE SAME
			IF (( SELECT COUNT(*) FROM ASSM_RELEASE_Lot_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSEMBLY_RELEASE_ID AND LOT_TRANSFERRED = 1) = 0 )
			BEGIN
					UPDATE ASSM_RELEASE_TABLE SET STATUS = 'Closed' WHERE ASSEMBLY_RELEASE_ID = @ASSEMBLY_RELEASE_ID
			END
	END
END

